package com.jdbc.crud;

import com.jdbc.utils.JDBCUtils;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

/**
 * @author wzm
 * @create 2023-10-31 14:24
 * project_name:web2310
 */
//专门用来测试增删改查的类
public class CrudTest {
    private Connection conn = null;
    private PreparedStatement ps = null;
    private ResultSet rs = null;

    //新增数据
    @Test
    public void insert() {
        try {
            //1.打开与数据库的连接
            conn = JDBCUtils.getConn();
            //2.定义sql语句
            String sql = "insert into test values(?,?)";
            //3.获取执行sql的对象
            ps = conn.prepareStatement(sql);
            //4.可选：为sql的占位符赋值
            ps.setInt(1, 0);
            ps.setString(2, "刘德华");
            //5.执行sql语句并得到结果
            int i = ps.executeUpdate();
            System.out.println("i = " + i);
            //6.根据结果进行封装或返回
            if (i > 0) {
                System.out.println("新增成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //7.关闭资源
            JDBCUtils.closeConn(conn, ps, null);
        }
    }

    //删除数据
    @Test
    public void delete() {
        try {
            conn = JDBCUtils.getConn();
            String sql = "delete from test where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, 3);
            int i = ps.executeUpdate();
            if (i > 0) {
                System.out.println("删除成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeConn(conn, ps, null);
        }

    }

    //修改数据
    @Test
    public void update() {
        try {
            conn = JDBCUtils.getConn();
            String sql = "update test set name = ? where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, "二狗");
            ps.setInt(2, 1);
            int i = ps.executeUpdate();
            if (i > 0) {
                System.out.println("修改成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeConn(conn, ps, null);
        }


    }

    //查询单个数据记录
    @Test
    public void selectById() {
        try {
            conn = JDBCUtils.getConn();
            String sql = "select id,name from test where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, 1);
            //结果集 是根据数据数据组成的一张表  需要手动的取出里面的数据封装为想要的结果
            rs = ps.executeQuery();

            //从结果集中获取值
            if (rs.next()) {
                //光标下移 有值
                //1.根据结果的索引取值
                //int id = rs.getInt(1);
                //String name = rs.getString(2);

                //2.根据数据库字段名获取值
                int id = rs.getInt("id");
                String name = rs.getString("name");

                //将获取的数据封装为对象
                com.jdbc.entity.Test test = new com.jdbc.entity.Test(id, name);
                System.out.println("查询到的对象test = " + test);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeConn(conn, ps, rs);
        }

    }

    //查询全部数据
    @Test
    public void selectByAll() {
        try {
            conn = JDBCUtils.getConn();
            String sql = "select id,name from test ";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            //查询的值可能有多行 循环移动光标循环取值
            List<com.jdbc.entity.Test> list = new ArrayList<>();
            while(rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                com.jdbc.entity.Test test = new com.jdbc.entity.Test(id, name);
                //每次获取的对象需要存储到容器中
                list.add(test);
            }
            System.out.println("list = " + list);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeConn(conn,ps,rs);
        }
    }

}
